﻿using System;
using System.Data.SqlClient;
using ECM7.NantContrib.Types;
using NAnt.Core.Attributes;

namespace ECM7.NantContrib.Tasks.SqlServer
{
	[TaskName("addDatabaseUsers")]
	public class AddDatabaseUsersTask : BaseSqlServerTask
	{
		[BuildElementArray("user", Required = true)]
		public DatabaseUserElement[] Users { get; set; }

		protected override void ExecuteTask()
		{
			SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
			{
				InitialCatalog = Database,
				DataSource = Instance,
				UserID = User ?? string.Empty,
				Password = Password ?? string.Empty,
				IntegratedSecurity = IntegratedSecurity
			};

			using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
			{
				connection.Open();

				foreach (var user in Users)
				{
					ProcessUser(connection, user);
				}
			}
		}

		private void ProcessUser(SqlConnection connection, DatabaseUserElement user)
		{
			if (user == null)
			{
				return;
			}

			using (var tran = connection.BeginTransaction())
			{
				try
				{
					AddLogin(tran, user);
					AddUser(tran, user);
					SetAsDbOwner(tran, user);

					tran.Commit();
				}
				catch (Exception ex)
				{
					tran.Rollback();
					LogError("Unable to create user '{0}': {1}", user, ex.Message);
				}
			}
		}

		private void AddLogin(SqlTransaction tran, DatabaseUserElement user)
		{
			string password = (user.Password ?? string.Empty).Replace("'", "''");
			string sqlLoginMethod = user.IsWindowsUser
			                        	? "FROM WINDOWS"
			                        	: "WITH PASSWORD = '{2}'";

			string sqlTemplate = "IF( NOT EXISTS (SELECT * FROM sys.server_principals " +
			             "WHERE name = {0} )) CREATE LOGIN [{1}] " + sqlLoginMethod;

			using (SqlCommand command = tran.Connection.CreateCommand())
			{
				command.Transaction = tran;
				command.CommandText = string.Format(sqlTemplate, "@login", user.Login, password);
				command.Parameters.AddWithValue("@login", user.Login);
				
				LogMessage(sqlTemplate, "@login", user.Login, "#####");
				command.ExecuteNonQuery();
			}
		}

		private void AddUser(SqlTransaction tran, DatabaseUserElement user)
		{
			const string SQL = "IF( NOT EXISTS (SELECT * FROM sys.database_principals " +
			                   "WHERE name = @login)) CREATE USER [{0}] FOR LOGIN [{0}]";

			using (SqlCommand command = tran.Connection.CreateCommand())
			{
				command.Transaction = tran;
				command.CommandText = string.Format(SQL, user.Login);
				command.Parameters.AddWithValue("@login", user.Login);
				
				ExecuteCommand(command);
			}
		}

		private void SetAsDbOwner(SqlTransaction tran, DatabaseUserElement user)
		{
			using (SqlCommand command = tran.Connection.CreateCommand())
			{
				command.Transaction = tran;
				command.CommandText = "EXEC sp_addrolemember N'db_owner', @login";
				command.Parameters.AddWithValue("@login", user.Login);
				
				ExecuteCommand(command);
			}
		}
	}
}
